package com.alcargo.tools.sample.main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.log4j.Logger;
import org.hsqldb.Server;

public class HsqlSampleMain {
	static Logger logger = Logger.getLogger("HsqlSampleMain");
	public static void main(String arg[]) throws Exception{
		HsqlSampleMain tmp = new HsqlSampleMain();
		tmp.serviceHsql();
	}
	
	public void memoryHsql()throws Exception{
		logger.debug("hsql start");
		Class.forName("org.hsqldb.jdbcDriver");
		Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:aname", "sa", "");
		Statement stmt = null;
        ResultSet rs = null;
        stmt = connection.createStatement();
        String sql;
        sql = "create cached table dong_test(xh varchar(10));";
        stmt.execute(sql);
        sql = 	"insert into dong_test(xh) values('Tom'); " +
        		"insert into dong_test(xh) values('Mary')";
        stmt.execute(sql);
        sql= "select * from dong_test";
        rs = stmt.executeQuery(sql);
        if(rs.next()){
        	logger.info(rs.getString(1));
        }
		logger.debug("hsql end");
	}
	public void serverHsql()throws Exception{
		logger.debug("server hsql begin");
		Class.forName("org.hsqldb.jdbcDriver");
		Connection conn  = 
			DriverManager.getConnection("jdbc:hsqldb:file:./src/test/testdb/mydb", "sa", "");		
		Statement stmt = null;
        ResultSet rs = null;
        stmt = conn.createStatement();
        String sql;
        sql = "create cached table dong_test(xh varchar(10));";
        stmt.execute(sql);
        sql = 	"insert into dong_test(xh) values('Tom'); " +
        		"insert into dong_test(xh) values('Mary')";
        stmt.execute(sql);
        sql= "select * from dong_test";
        rs = stmt.executeQuery(sql);
        while(rs.next()){
        	logger.info(rs.getString(1));
        }
	
		logger.debug("server hsql end");
	}
	public void serviceHsql(){
//	        String serverProps;
	        String url;  //数据库地址
//	        String user = "sa";  //登录数据库时的用户名称
//	        String password = "";  //登录数据库时的用户密码
	        Server server;
//	        boolean isNetwork = true;
	
	        url = "jdbc:hsqldb:hsql://localhost/test";
	        server = new Server();
	        server.setDatabaseName(0, "test"); //设置数据库名称，这两个test是数据库名，需一致。
	        server.setDatabasePath(0, "mem:ss;sql.enforce_strict_size=true");  
	        //注：这里的ss是我随便写的，只要不是空就行，如果是空，出现如下异常：java.sql.SQLException: socket creation error
	        server.setLogWriter(null);  //不知道什么意思，好像是控制某些输出
	        server.setErrWriter(null);  //不知道什么意思，好像是控制某些输出，有心的朋友可以查查资料，顺便告诉我一下
	        server.start();  //服务启动
	        
	        String serverInfo = server.toString();
	        System.out.println(">>> serverInfo: " + serverInfo);
	        System.out.println(">>> the server is being started");
	
	        try {
	            Class.forName("org.hsqldb.jdbcDriver");  //获取驱动
	
	            Connection connection = DriverManager.getConnection(url, "sa", "");
	            Statement statement = connection.createStatement();
	            String sql1 = "create table dong_test(xh varchar(10));";
	            String sql2 = "insert into dong_test(xh) values('Tom'); insert into dong_test(xh) values('Mary')";
	            String sql3 = "select * from dong_test";
	            statement.executeUpdate(sql1);  //执行，创建数据表dong_test
	            statement.executeUpdate(sql2);  //向表中插入数据
	            ResultSet rs;
	            rs = statement.executeQuery(sql3);  //返回插入的数据集
	
	            while(rs.next()){
	                System.out.println(">>> "+rs.getString(1));
	            }
	            
	        } catch (Exception e) {
	            e.printStackTrace();
	        }finally{
	        	server.stop();
	        }
	       
        }
	
}
